//
//  PlaylistItemDBManager.swift
//  readtool
//
//  Created by Wan Jiading on 2017/8/1.
//  Copyright © 2017年 DAD. All rights reserved.
//	我的歌单内音频数据库管理器

import UIKit
import FMDB

class PlaylistItemDBManager: NSObject {
	
	static var dbQueue = DBManager.getDBQueue()
	
	// MARK: - SQL
	
	/// 获取最后一条PlaylistItem
	private static let getLastPlaylistItemSQL = "SELECT max(order_num) as maxId, * FROM t_playlist_item where playlist_type = ? AND playlist_id = ? AND member_uuid = ? order by order_num desc;"
	/// 获取PlaylistItem
	private static let getPlaylistItemSQL = "SELECT * FROM t_playlist_item where playlist_type = ? AND audio_id = ? AND member_uuid = ?;"
	/// 获取PlaylistItem列表
	private static let getPlaylistItemListSQL = "SELECT * FROM t_playlist_item where playlist_type = ? AND member_uuid = ?;"
	/// 更新是否下载
	private static let updateDownloadSQL = "UPDATE t_playlist_item SET is_download = ? WHERE audio_id = ?;"
	/// 更新是否缓存
	private static let updateCacheSQL = "UPDATE t_playlist_item SET is_cache = ? WHERE audio_id = ?;"
	/// 获取数量
	private static let getPlaylistItemCountSQL = "SELECT count(*) as count FROM t_playlist_item WHERE playlist_type = ? AND playlist_id = ? AND member_uuid = ?;"
	/// 删除一条歌单item
	private static let deletePlaylistItemSQL = "DELETE FROM t_playlist_item WHERE id = ?;"

	// MARK: - Query
	
	// MARK: 检测某音频是否在下载列表中
	/// 检测某音频是否在下载列表中
	///
	/// - Parameters:
	///   - playlistItem: 传入 audio.id, member_uuid
	///   - comletion: (PlaylistItemModel, errorMsg: String)
	class func getPlaylistItem(_ playlistType: PlaylistType, _ playlistItem: PlaylistItemModel, comletion:((_ playlistItem:PlaylistItemModel?, _ errorMsg:String?)->())) {
		dbQueue.inDatabase { (db) in
			let result = self.getPlaylistItem(db, playlistType, playlistItem)
			comletion(result.playlistItem, result.errorMsg)
		}
	}
	
	// MARK: 获取数量
	/// 获取数量
	class func getPlaylistItemCount(_ playlistType: PlaylistType, _ playlistID: String, _ memberUUID: String, completion:(Int32, String?)->()) {
		dbQueue.inDatabase { (db) in
			if let rs = db.executeQuery(getPlaylistItemCountSQL,
			                            withArgumentsIn: [playlistType.rawValue,
			                                              playlistID,
			                                              memberUUID]) {
				if rs.next() {
					let count = rs.int(forColumn: "count")
					completion(count, nil)
					rs.close()
					return
				}
				rs.close()
			}
			
			let errorMsg = "数据库语句   获取数据报错"
			ErrorLog(errorMsg)
			completion(0, errorMsg)
		}
	}
	
	// MARK: 获取歌单列表
	
	/// 获取歌单列表
	class func getPlaylistItemList(_ playlistType: PlaylistType, _ memberUUID: String, completion: ([PlaylistItemModel]?, String?)->()) {
		dbQueue.inDatabase { (db) in
			if let rs = db.executeQuery(getPlaylistItemListSQL,
					  withArgumentsIn: [playlistType.rawValue,
					                    memberUUID]) {
				let list = self.bindPlaylistItems(rs)
				completion(list, nil)
				rs.close()
			} else {
				ErrorLog("数据库语句   获取数据报错")
				completion(nil, "数据库语句   获取数据报错")
			}
		}
	}
	
	// MARK: 获取某个类型下最后的PlaylistItem
	
	/// 获取某个类型下最后的PlaylistItem
	///
	/// - Parameters:
	///   - playlistItem: 传入 id, member_uuid
	private class func getLastPlaylistItem(_ db: FMDatabase, _ playlistType: PlaylistType, _ playlistItem: PlaylistItemModel) -> PlaylistItemModel? {
		var playlistItemModel: PlaylistItemModel?
		let rs = db.executeQuery(getLastPlaylistItemSQL,
				 withArgumentsIn: [playlistType.rawValue,
								   playlistItem.id!,
								   playlistItem.member_uuid!])
		if let playlistItemList = bindPlaylistItems(rs) {
			playlistItemModel = playlistItemList.first
		}
		return playlistItemModel
	}
	
	// MARK: 获取某个PlaylistItem
	
	/// 获取某个PlaylistItem
	///
	/// - Parameters:
	///   - playlistItem: 传入 audio.id, member_uuid
	private class func getPlaylistItem(_ db: FMDatabase, _ playlistType: PlaylistType, _ playlistItem: PlaylistItemModel) -> (playlistItem:PlaylistItemModel?, errorMsg:String?) {
		var playlistItemModel: PlaylistItemModel?
		var msg: String?
		if let rs = db.executeQuery(getPlaylistItemSQL,
				   withArgumentsIn: [playlistType.rawValue,
									 playlistItem.audio!.id!,
									 playlistItem.member_uuid!]) {
			if let playlistItemList = bindPlaylistItems(rs) {
				playlistItemModel = playlistItemList.first
			}
		} else {
			msg = "数据库语句   获取数据报错"
			ErrorLog(msg)
		}
		return (playlistItemModel, msg)
	}
	
	// MARK: 绑定 [PlaylistItem]
	
	/// 绑定 [PlaylistItem]
	private class func bindPlaylistItems(_ resultSet: FMResultSet?) -> [PlaylistItemModel]? {
		if let rs = resultSet {
			var playlistItemList = [PlaylistItemModel]()
			while rs.next() {
				let playlistItemModel = PlaylistItemModel()
				playlistItemModel.code = rs.int(forColumn: "code")
				playlistItemModel.id = String(rs.int(forColumn: "id"))
				playlistItemModel.order_num = rs.int(forColumn: "order_num")
				if let typeStr = rs.string(forColumn: "playlist_type") {
					if let type = PlaylistType(rawValue: typeStr) {
						playlistItemModel.playlist_type = type
					}
				}
				playlistItemModel.playlist_id = rs.string(forColumn: "playlist_id")
				playlistItemModel.member_uuid = rs.string(forColumn: "member_uuid")
				playlistItemModel.is_download = rs.int(forColumn: "is_download") == 1
				playlistItemModel.is_cache = rs.int(forColumn: "is_cache") == 1
				playlistItemModel.file_path = rs.string(forColumn: "file_path")
				
				let audioModel = AudioModel()
				audioModel.id = rs.string(forColumn: "audio_id")
				audioModel.name = rs.string(forColumn: "audio_name")
				audioModel.pic_url = rs.string(forColumn: "audio_pic_url")
				audioModel.track_name = rs.string(forColumn: "track_name")
				audioModel.version = rs.string(forColumn: "audio_version")
				
				playlistItemModel.audio = audioModel
				
				playlistItemList.append(playlistItemModel)
			}
			return playlistItemList
		}
		return nil
	}
	
	// MARK: - Update
	
	/// 更新audioID是否下载
	class func updateIsDownload(_ audioID: String, _ download: Bool, completion:execUpdateCompletion) {
		dbQueue.inDatabase { (db) in
			let updateToIsDownloadSuccess =
				db.executeUpdate(updateDownloadSQL,
				                 withArgumentsIn: [download ? 1 : 0,
												   audioID,])
			if updateToIsDownloadSuccess {
				completion(true, nil)
			} else {
				ErrorLog("数据库语句   更新数据报错")
				completion(false, "数据库语句   更新数据报错")
			}
		}
	}
	
	/// 更新audioID是否缓存
	class func updateIsCache(_ audioID: String, _ cache: Bool, completion:execUpdateCompletion) {
		dbQueue.inDatabase { (db) in
			let updateToIsDownloadSuccess =
				db.executeUpdate(updateCacheSQL,
				                 withArgumentsIn: [cache ? 1 : 0,
				                                   audioID,])
			if updateToIsDownloadSuccess {
				completion(true, nil)
			} else {
				ErrorLog("数据库语句   更新数据报错")
				completion(false, "数据库语句   更新数据报错")
			}
		}
	}
	
	// MARK: 更改排序
	/**
	更改排序
	
	- parameter sourceCode:      原.code
	- parameter sourceRownumber: 原.row_number
	- parameter targetCode:      目标.code
	- parameter targetRownumber: 目标.row_number
	*/
	class func updatePlaylistItemOrder(_ memberUUID: String, _ playlistType: PlaylistType, _ sourceCode: Int, _ sourceRownumber: Int, _ targetCode: Int , _ targetRownumber: Int, completion:(Bool, String?) -> ()) {
		dbQueue.inDatabase { (db) in
			var success = false
			if sourceRownumber < targetRownumber {
				var sql = "update t_book_audio set order_num = order_num -1 where order_num <= \(targetRownumber) and order_num > \(sourceRownumber) and member_uuid = '\(memberUUID)' AND list_type = '\(playlistType.rawValue)';"
				DebugLog(sql)
				success = db.executeUpdate(sql, withArgumentsIn: [])
				if success {
					sql = "update t_book_audio set order_num = \(targetRownumber) where id = \(sourceCode) and member_uuid = '\(memberUUID)' AND list_type = '\(playlistType.rawValue)';"
					DebugLog(sql)
					success = db.executeUpdate(sql, withArgumentsIn: [])
				}
			} else {
				var sql = "update t_book_audio set order_num = order_num + 1 where order_num < \(sourceRownumber) and order_num >= \(targetRownumber) and member_uuid = '\(memberUUID)' AND list_type = '\(playlistType.rawValue)';"
				DebugLog(sql)
				success = db.executeUpdate(sql, withArgumentsIn: [])
				if success {
					sql = "update t_book_audio set order_num = \(targetRownumber) where id = \(sourceCode) and member_uuid = '\(memberUUID)' AND list_type = '\(playlistType.rawValue)';"
					DebugLog(sql)
					success = db.executeUpdate(sql, withArgumentsIn: [])
				}
			}
			
			if success {
				completion(true, nil)
			} else {
				ErrorLog("数据库语句   更改排序报错")
				completion(false, "数据库语句   更改排序报错")
			}
		}
	}
	
	// MARK: - Delete
	
	/// 删掉一条歌单item
	class func deletePlaylistItem(_ id: String, completion:(Bool, String?)->()) {
		dbQueue.inDatabase { (db) in
			let success = db.executeUpdate(deletePlaylistItemSQL,
			                               withArgumentsIn: [id])
			if success {
				completion(true, nil)
			} else {
				ErrorLog("数据库语句   删除数据报错")
				completion(false, "数据库语句   删除数据报错")
			}
		}
	}
	
	// MARK: - Insert
	
	// MARK: 添加1条PlaylistItem数据
	
	/// 添加数据
	class func addPlaylistItem(_ playlistType: PlaylistType, _ playlistItem: PlaylistItemModel, completion: execUpdateCompletion) {
		dbQueue.inDatabase { (db) in
			/// 添加PlaylistItem
			let addPlaylistItemSQL = "INSERT INTO t_playlist_item (" +
				"playlist_type, playlist_id, member_uuid, id, " +
				"audio_id, audio_name, audio_pic_url, audio_version, track_name, " +
				"file_path, is_download, is_cache, order_num, other_data)" +
				"VALUES" +
				"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
			
			// 游客，下载
			if playlistItem.member_uuid! == "0" || playlistType == .download {
				setupPlaylistItemWithGuest(db, playlistType, playlistItem)
			}
			
			// 当前记录是否已下载
			var isDownload = false
			var isCache = false
			if let existPlaylistItemModel = self.getPlaylistItem(db, playlistType, playlistItem).playlistItem {
				isDownload = existPlaylistItemModel.is_download
				isCache = existPlaylistItemModel.is_cache
			}
			
			let success = db.executeUpdate(addPlaylistItemSQL,
			                               withArgumentsIn: [playlistItem.playlist_type.rawValue,
			                                                 playlistItem.playlist_id!,
			                                                 playlistItem.member_uuid!,
			                                                 playlistItem.id!,
			                                                 
			                                                 playlistItem.audio!.id!,
			                                                 playlistItem.audio!.name!,
			                                                 playlistItem.audio!.pic_url!,
			                                                 playlistItem.audio!.version!,
			                                                 playlistItem.audio!.track_name!,
			                                                 
			                                                 playlistItem.file_path!,
			                                                 isDownload,
			                                                 isCache,
			                                                 playlistItem.order_num,
			                                                 playlistItem.other_data ?? ""])
			if !success {
				ErrorLog("数据库语句   插入数据报错")
				completion(false, "数据库语句   插入数据报错")
				return
			}
			
			// 如果是添加了我的下载，则更新所有音频为已下载
			if playlistType == .download {
				self.updateIsDownload(playlistItem.audio!.id!, true, completion: { (sucess, msg) in
					completion(success, msg)
				})
			} else {
				completion(success, nil)
			}
		}
	}

	/// 游客身份，需要调整PlaylistItem部分属性
	private class func setupPlaylistItemWithGuest(_ db: FMDatabase, _ playlistType: PlaylistType, _ playlistItem: PlaylistItemModel) {
		
		// 最后一条记录,游客需要自己维护数据，包括id等
		if let lastPlaylistItem = self.getLastPlaylistItem(db, playlistType, playlistItem) {
			playlistItem.order_num = lastPlaylistItem.order_num + 1
			playlistItem.playlist_id = lastPlaylistItem.playlist_id
			if let idStr = lastPlaylistItem.id {
				if let id = Int32(idStr) {
					playlistItem.id = String(id + 1)
				}
			}
		} else {
			playlistItem.order_num = 1
			playlistItem.id = "1"
		}
	}
	
	// MARK: - 创建表
	class func createPlaylistItemTable() {
		dbQueue.inDatabase { (db) in
			let sql = "CREATE TABLE IF NOT EXISTS t_playlist_item (" +
				"'code' integer  PRIMARY KEY," +
				"'playlist_type' TEXT," +
				"'playlist_id' TEXT," +
				"'member_uuid' TEXT," +
				"'id' integer," +
				"'audio_id' TEXT," +
				"'audio_name' TEXT," +
				"'audio_pic_url' TEXT," +
				"'audio_version' TEXT," +
				"'track_name' TEXT," +
				"'file_path' TEXT," +
				"'is_download' integer," +
				"'is_cache' integer," +
				"'order_num' integer," +
				"'other_data' TEXT);"
			if !db.executeUpdate(sql, withArgumentsIn: []) {
				ExceptionLog("t_play_list 创建失败")
			}
		}
	}
}
